/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sonth.DAL;

import sonth.Utils.JdbcConnector;
import sonth.EL.Product;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Administrator
 */
public final class ProductDAO {

    /**
     * desc.
     */
    private ProductDAO() {
    }

    /**
     * desc.
     * @return List<Product> desc
     * @throws Exception desc
     */
    public static List<Product> selectAll() throws Exception {
        List<Product> products = new ArrayList<Product>();

        Connection con = JdbcConnector.open();
        String sql = "SELECT * FROM Product";
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()) {
            int id = rs.getInt("ID");
            String name = rs.getString("Name");
            String description = rs.getString("Description");
            int price = rs.getInt("Price");

            Product product = new Product(id, name, description, price);
            products.add(product);
        }
        JdbcConnector.close(con);

        return products;
    }

    /**
     * desc.
     * @param keyword desc
     * @return List<Product> desc
     * @throws Exception desc
     */
    public static List<Product> selectByName(final String keyword)
            throws Exception {
        List<Product> products = new ArrayList<Product>();

        Connection con = JdbcConnector.open();
        String sql = "SELECT * FROM Product WHERE Name LIKE ?";
        PreparedStatement pst = con.prepareStatement(sql);
        int index = 1;
        pst.setString(index++, "%" + keyword + "%");
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("ID");
            String name = rs.getString("Name");
            String description = rs.getString("Description");
            int price = rs.getInt("Price");

            Product product = new Product(id, name, description, price);
            products.add(product);
        }
        JdbcConnector.close(con);

        return products;
    }

    /**
     * desc.
     * @param range desc
     * @return List<Product> desc
     * @throws Exception desc
     */
    public static List<Product> selectByAlphabetName(final String range)
            throws Exception {
        List<Product> products = new ArrayList<Product>();

        Connection con = JdbcConnector.open();
        String sql = "SELECT * FROM Product WHERE Name LIKE ?";
        PreparedStatement pst = con.prepareStatement(sql);
        int index = 1;
        pst.setString(index++, "[" + range + "]%");
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("ID");
            String name = rs.getString("Name");
            String description = rs.getString("Description");
            int price = rs.getInt("Price");

            Product product = new Product(id, name, description, price);
            products.add(product);
        }
        JdbcConnector.close(con);

        return products;
    }

    /**
     * desc.
     * @param product desc
     * @return boolean desc
     * @throws Exception desc
     */
    public static boolean insert(final Product product) throws Exception {
        boolean result = false;

        Connection con = JdbcConnector.open();
        String sql = "INSERT INTO Product(Name,Description,Price)"
                + " VALUES(?,?,?)";
        PreparedStatement pst = con.prepareStatement(sql);
        int index = 1;
        pst.setString(index++, product.getName());
        pst.setString(index++, product.getDescription());
        pst.setInt(index++, product.getPrice());
        int rows = pst.executeUpdate();
        if (rows > 0) {
            result = true;
        }
        JdbcConnector.close(con);

        return result;
    }
}
